﻿/*
* 创建者：LIYINFAI
* 创建时间：2023/10/24 9:35:26
* 描述：
* --------------------------------------
* 修改说明：
* --------------------------------------
*/
using SQLiteEx;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace CommonLibrary
{
    /// <summary>
    /// 产品数据库操作
    /// </summary>
    public class ProductSQLiteDb : SQLiteHelper
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbPath"></param>
        public ProductSQLiteDb(string dbPath) : base(dbPath)
        {
        }
        /// <summary>
        /// 实例方法
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public static ProductSQLiteDb Instance(int year = -1)
        {
            year = year == -1 ? DateTime.Today.Year : year;
            string dbFile = Path.Combine(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "dbs"), $"{ProductFileManager.DB_PREV_NAME}_{year}.db");
            CommonHelper.CheckDirectory(dbFile);
            ProductSQLiteDb db = null;
            if (CommonHelper.CheckFile(dbFile))
            {
                db = new ProductSQLiteDb(dbFile);
                db.CreateTable<ProductInfo>();
                db.CreateIndex("products", new string[] { "BarCode" });
                db.CreateIndex("products", new string[] { "BarCode", "EndTime" });
            }
            else
            {
                db = new ProductSQLiteDb(dbFile);
            }
            return db;
        }
        /// <summary>
        /// 获取产品列表
        /// </summary>
        /// <param name="count"></param>
        /// <param name="page"></param>
        /// <returns></returns>
        public static List<ProductInfo> GetTodayProductInfoList(int count = 100, int page = 1)
        {
            DateTime now = DateTime.Now;
            DateTime today = DateTime.Today;
            DateTime tomorrow = today.AddDays(1);
            TimeSpan time = now.TimeOfDay;
            TimeSpan startTime = TimeSpan.Parse("08:00:00");
            TimeSpan endTime = TimeSpan.Parse("20:00:00");
            var startTimeCondition = time.Subtract(startTime).TotalSeconds >= 0 && endTime.Subtract(time).TotalSeconds >= 0 ? $"{today.ToString("yyyy-MM-dd")} 08:00:00" : $"{today.ToString("yyyy-MM-dd")} 20:00:01";
            var endTimeCondition = time.Subtract(startTime).TotalSeconds >= 0 && endTime.Subtract(time).TotalSeconds >= 0 ? $"{today.ToString("yyyy-MM-dd")} 20:00:00" : $"{tomorrow.ToString("yyyy-MM-dd")} 07:59:59";
            return GetProductInfoListByScanTime(startTimeCondition, endTimeCondition, count, page);
        }
        /// <summary>
        /// 根据条形码，扫码时间，出站时间获取产品列表
        /// </summary>
        /// <param name="barcode"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="count"></param>
        /// <param name="page"></param>
        /// <param name="isDesc"></param>
        /// <returns></returns>
        public static List<ProductInfo> GetProductInfoList(string barcode, string startTime, string endTime, int count = 100, int page = 1, bool isDesc = true)
        {
            List<ProductInfo> list = new List<ProductInfo>();
            try
            {
                DateTime startDateTime = DateTime.Now;
                DateTime endDateTime = DateTime.Now;
                if (DateTime.TryParse(startTime, out startDateTime) && DateTime.TryParse(endTime, out endDateTime) && endDateTime.Subtract(startDateTime).TotalSeconds >= 0)
                {
                    int startYear = startDateTime.Year;
                    int endYear = endDateTime.Year;
                    for (int i = endYear; i >= startYear; i--)
                    {
                        using (ProductSQLiteDb db = ProductSQLiteDb.Instance(i))
                        {
                            try
                            {
                                string sql = $"SELECT * FROM products WHERE endtime>='{startTime}' AND endtime<='{endTime}' ORDER BY id {(isDesc?"DESC":"")} LIMIT {(page - 1) * 100},{count};";
                                sql = !string.IsNullOrWhiteSpace(barcode) ? $"SELECT * FROM products WHERE barcode LIKE '%{barcode}%' AND endtime>='{startTime}' AND endtime<='{endTime}' ORDER BY id {(isDesc ? "DESC" : "")} LIMIT {(page - 1) * 100},{count};" : sql;
                                var tempList = db.ToList<ProductInfo>(sql);
                                if (tempList != null && tempList.Count > 0)
                                {
                                    list.AddRange(tempList);
                                    list = list.OrderBy(v => v.Id).ToList();
                                }
                            }
                            catch (Exception innerEx)
                            {
                                LogHelper.Error(innerEx);
                            }
                        }
                    }
                }
                if (list != null && list.Count > 0)
                {
                    foreach (var l in list)
                    {
                        l.Format();
                    }
                }
            }
            catch (Exception ex)
            {
                list = new List<ProductInfo>();
                LogHelper.Error(ex);
            }
            return list;
        }
        /// <summary>
        /// 根据条码，开始时间跟结束时间来获取所有数据
        /// </summary>
        /// <param name="barcode"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<ProductInfo> GetProductInfoAllList(string barcode, string startTime, string endTime)
        {
            List<ProductInfo> list = new List<ProductInfo>();
            try
            {
                DateTime startDateTime = DateTime.Now;
                DateTime endDateTime = DateTime.Now;
                if (DateTime.TryParse(startTime, out startDateTime) && DateTime.TryParse(endTime, out endDateTime) && endDateTime.Subtract(startDateTime).TotalSeconds >= 0)
                {
                    int startYear = startDateTime.Year;
                    int endYear = endDateTime.Year;
                    for (int i = endYear; i >= startYear; i--)
                    {
                        using (ProductSQLiteDb db = ProductSQLiteDb.Instance(i))
                        {
                            try
                            {
                                string sql = $"SELECT * FROM products WHERE endtime>='{startTime}' AND endtime<='{endTime}' ORDER BY id;";
                                sql = !string.IsNullOrWhiteSpace(barcode) ? $"SELECT * FROM products WHERE barcode LIKE '%{barcode}%' AND endtime>='{startTime}' AND endtime<='{endTime}' ORDER BY id;" : sql;
                                var tempList = db.ToList<ProductInfo>(sql);
                                if (tempList != null && tempList.Count > 0)
                                {
                                    list.AddRange(tempList);
                                }
                            }
                            catch (Exception innerEx)
                            {
                                LogHelper.Error(innerEx);
                            }
                        }
                    }
                }
                if (list != null && list.Count > 0)
                {
                    foreach (var l in list)
                    {
                        l.Format();
                    }
                }
            }
            catch (Exception ex)
            {
                list = new List<ProductInfo>();
                LogHelper.Error(ex);
            }
            return list;
        }
        /// <summary>
        /// 根据扫描时间获取产品列表
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="count"></param>
        /// <param name="page"></param>
        /// <returns></returns>
        public static List<ProductInfo> GetProductInfoListByScanTime(string startTime, string endTime, int count = 100, int page = 1)
        {
            return GetProductInfoList(string.Empty, startTime, endTime, count, page);
        }
        /// <summary>
        /// 增加产品
        /// </summary>
        /// <param name="info"></param>
        public static void AddProductInfo(ProductInfo info)
        {
            try
            {
                using (ProductSQLiteDb db = Instance(DateTime.Today.Year))
                {
                    db.Insert(info);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
            }
        }
        /// <summary>
        /// 批量增加产品数据
        /// </summary>
        /// <param name="list">需要写入的产品数据</param>
        public static void AddProductList(List<ProductInfo> list)
        {
            try
            {
                using (ProductSQLiteDb db = ProductSQLiteDb.Instance(DateTime.Today.Year))
                {
                    db.InsertAll(list);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex);
            }
        }
    }
}
